"""Add lead_intentions table for multi-intention support

Revision ID: 20250117_add_intentions
Revises: 7a9659384fc4
Create Date: 2025-01-17 10:00:00.000000

业务说明：
- Lead表的主意向（demand_type, budget_range）为必填项
- LeadIntention表为可选补充，存储额外意向
- 关系：1个客户可以有0-N个额外意向
"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '20250117_add_intentions'
down_revision = '20251011_soft_delete'
branch_labels = None
depends_on = None


def upgrade():
    # 创建 lead_intentions 表
    op.create_table(
        'lead_intentions',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('lead_id', sa.Integer(), nullable=False),

        # 意向内容
        sa.Column('demand_type',
                  sa.Enum('rent_whole', 'rent_shared', 'rent_short', 'buy', 'sell',
                         name='demandtype'),
                  nullable=False),
        sa.Column('budget_min', sa.Integer(), nullable=True),
        sa.Column('budget_max', sa.Integer(), nullable=True),
        sa.Column('area_preferences', sa.String(500), nullable=True,
                  comment='区域偏好（JSON数组字符串）'),
        sa.Column('layout', sa.String(50), nullable=True,
                  comment='户型需求（如: 2室1厅）'),
        sa.Column('requirement_tags', sa.String(500), nullable=True,
                  comment='需求标签（JSON数组字符串）'),

        # 优先级和意向度
        sa.Column('priority', sa.Integer(), nullable=False, server_default='1',
                  comment='优先级（1-10，1最高）'),
        sa.Column('intention_level',
                  sa.Enum('high', 'medium', 'low', 'none', name='intentionlevel'),
                  nullable=False, server_default='medium',
                  comment='意向度'),

        # 备注
        sa.Column('notes', sa.Text(), nullable=True,
                  comment='该意向的详细备注'),

        # 时间戳
        sa.Column('created_at', sa.DateTime(), nullable=False,
                  server_default=sa.text('CURRENT_TIMESTAMP')),
        sa.Column('updated_at', sa.DateTime(), nullable=False,
                  server_default=sa.text('CURRENT_TIMESTAMP')),

        # 主键和外键
        sa.PrimaryKeyConstraint('id'),
        sa.ForeignKeyConstraint(['lead_id'], ['leads.id'],
                                name='fk_lead_intentions_lead_id',
                                ondelete='CASCADE'),
    )

    # 创建索引（提升查询性能）
    op.create_index('ix_lead_intentions_lead_id', 'lead_intentions', ['lead_id'])
    op.create_index('ix_lead_intentions_demand_type', 'lead_intentions', ['demand_type'])
    op.create_index('ix_lead_intentions_budget_min', 'lead_intentions', ['budget_min'])
    op.create_index('ix_lead_intentions_budget_max', 'lead_intentions', ['budget_max'])
    op.create_index('ix_lead_intentions_intention_level', 'lead_intentions', ['intention_level'])


def downgrade():
    # 删除索引
    op.drop_index('ix_lead_intentions_intention_level', table_name='lead_intentions')
    op.drop_index('ix_lead_intentions_budget_max', table_name='lead_intentions')
    op.drop_index('ix_lead_intentions_budget_min', table_name='lead_intentions')
    op.drop_index('ix_lead_intentions_demand_type', table_name='lead_intentions')
    op.drop_index('ix_lead_intentions_lead_id', table_name='lead_intentions')

    # 删除表
    op.drop_table('lead_intentions')
